
create external table jms_dm.dm_lmdm_four_segment_monitoring_sum_dt (
   date_time string comment '日期',
   sign_finance_code string comment '签收财务网点编码',
   sign_finance_name string comment '签收财务网点名称',
   sign_network_code string comment '签收网点code',
   sign_network_name string comment '签收网点名称',
   receiver_province_id string comment '省份id',
   receiver_province_name string comment '省份name',
   receiver_city_id string comment '城市id',
   receiver_city_name string comment '城市name',
   receiver_area_id string comment '区域id',
   receiver_area_name string comment '区域name',
   courier_station_name string comment '驿站名称',
   input_type_name string comment '驿站品牌',
   courier_station_code string comment '驿站编码',
   station_unique_number string comment '驿站唯一标识',
   post_count bigint comment '驿站总票数',
   parse_post_count bigint comment '驿站码解析票数',
   true_post_count bigint comment '驿站码解析票数',
   qxt_parse_count bigint comment '七星潭解析票数',
   thd_parse_count bigint comment '桃花岛解析票数',
   yl_parse_count bigint comment '云路解析票数',
   qxt_parse_true_count bigint comment '七星潭解析准确票数',
   thd_parse_true_count bigint comment '桃花岛解析准确票数',
   yl_parse_true_count bigint comment '云路解析准确票数',   
   qxt_total_count bigint comment '七星潭总票数',
   thd_total_count bigint comment '桃花岛总票数',
   yl_total_count bigint comment '云路总票数'  ,
   station_code string comment '签收驿站编码'

) COMMENT '驿站码解析监控'
PARTITIONED BY (dt STRING COMMENT '')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_lmdm_four_segment_monitoring_sum_dt'
TBLPROPERTIES (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);
       
       

       

create  table jms_dm.dm_lmdm_four_segment_monitoring_sum_dt (
   date_time date comment '日期',
   sign_network_code varchar(300) comment '签收网点code',
   sign_finance_code varchar(300) comment '签收财务网点编码',
   sign_finance_name varchar(300) comment '签收财务网点名称',
   sign_network_name varchar(300) comment '签收网点名称',
   receiver_province_id varchar(300) comment '省份id',
   receiver_province_name varchar(300) comment '省份name',
   receiver_city_id varchar(300) comment '城市id',
   receiver_city_name varchar(300) comment '城市name',
   receiver_area_id varchar(300) comment '区域id',
   receiver_area_name varchar(300) comment '区域name',
   courier_station_name varchar(300) comment '驿站名称',
   input_type_name varchar(300) comment '驿站品牌',
   courier_station_code varchar(300) comment '驿站编码',
   station_unique_number varchar(300) comment '驿站唯一标识',
   post_count bigint comment '驿站总票数',
   parse_post_count bigint comment '驿站码解析票数',
   true_post_count bigint comment '驿站码解析票数',
   qxt_parse_count bigint comment '七星潭解析票数',
   thd_parse_count bigint comment '桃花岛解析票数',
   yl_parse_count bigint comment '云路解析票数',
   qxt_parse_true_count bigint comment '七星潭解析准确票数',
   thd_parse_true_count bigint comment '桃花岛解析准确票数',
   yl_parse_true_count bigint comment '云路解析准确票数' ,
   qxt_total_count bigint comment '七星潭总票数',
   thd_total_count bigint comment '桃花岛总票数',
   yl_total_count bigint comment '云路总票数'  ,
   station_code varchar(300) comment '签收驿站编码'

   
  )
ENGINE=OLAP
DUPLICATE KEY(date_time,sign_network_code,sign_finance_code)
comment '驿站码解析监控'
PARTITION BY RANGE (date_time) (
   START ('2023-06-01') END ('2023-06-30') EVERY (INTERVAL 1 day))
   DISTRIBUTED BY HASH(date_time,sign_network_code) BUCKETS 4
PROPERTIES (
'replication_num' = '1',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-180',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '4',
'in_memory' = 'false',
'storage_format' = 'V2'
)    